<?php

namespace app\services\data\stat\ad\normal;

// 注册转端数
class StatToapkReg extends StatBaseUserEvent
{
    public function getSql()
    {
        $join             = $this->queryJoin('users');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'time'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $today_where      = "toDate(users.`#create_time`)=toDate(users.`toapk_time`)";

        $sql = "
            SELECT count(DISTINCT `#user_id`) AS toapk_reg FROM users
            {$join}
            WHERE {$user_where}
            AND {$plan_where}
            AND {$media_user_where}
            AND toapk_time>1
            AND {$today_where}
        ";

        return $sql;
    }

    protected function queryGroupBy($table_name = 'users')
    {
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                    $selects[] = "formatDateTime(toDateTime(users.`#create_time`), '%H', '{$this->timezone}') AS stat_hour";
                    break;
                case 'stat_date':
                    $selects[] = "toDate(users.`#create_time`, '{$this->timezone}') AS stat_date";
                    break;
                case 'stat_week':
                    $selects[] = "formatDateTime(toDateTime(users.`#create_time`), '%Y-%V', '{$this->timezone}') AS stat_week";
                    break;
                case 'stat_month':
                    $selects[] = "formatDateTime(toDateTime(users.`#create_time`), '%Y-%m', '{$this->timezone}') AS stat_month";
                    break;
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                    $selects[] = "ad_plan.{$name} AS {$name}";
                    break;
                case 'daili_id':
                    $selects[] = "ad_media_user.{$name} AS {$name}";
                    break;
                case 'adgroup_id':
                    $selects[] = "firstups.adgroup_id AS {$name}";
                    break;
                case 'ad_id':
                    $selects[] = "firstups.ad_id AS {$name}";
                    break;
                case 'creative_id':
                    $selects[] = "firstups.creative_id AS {$name}";
                    break;
                case 'media_user_id':
                    $selects[] = "firstups.{$name} AS {$name}";
                    break;
                default:
                    $selects[] = "{$table_name}.{$name} AS {$name}";
                    break;
            }
        }

        $select_str  = implode(',', $selects);
        $groupby_str = implode(',', $this->groupbys);

        return [$select_str, $groupby_str];
    }

    public function getSqlByGroup()
    {
        $join             = $this->queryJoin('users');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $today_where      = "toDate(users.`#create_time`)=toDate(users.`toapk_time`)";

        list($select_str, $groupby_str) = $this->queryGroupBy();

        $sql = "
            SELECT {$select_str}, count(DISTINCT users.`#user_id`) AS toapk_reg
            FROM users
            {$join}
            WHERE {$user_where}
            AND {$plan_where}
            AND {$media_user_where}
            AND toapk_time>1
            AND {$today_where}
            GROUP BY {$groupby_str}
        ";

        return $sql;
    }
}
